package com.lw.oop.dayfive.bdemo;

import java.sql.*;

/**
 * @author liuwei
 * @ClassName JdbcTest.java
 * @description: TODO
 * @date 2023年06月19日
 * @version: 1.0
 */
public class JdbcTest {
    public static void main(String[] args) throws SQLException {
        Statement statement = null;
        Connection conn = null;
        ResultSet result2 = null;
        try {
            //加载驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
            String url = "jdbc:mysql://localhost:3306/jdbc_test?useUnicode=true&characterEncoding=utf8";
            String user = "root";
            String password = "root";
            //获取执行者
            conn = DriverManager.getConnection(url, user, password);
            if (conn != null) {
                System.out.println("连接成功");
                //获取发送 SQL 的对象
                statement = conn.createStatement();
                System.out.println("***************添加数据******************");
                //添加数据
                String sql1 = "insert into student values (2023006,'猪八戒01','男',20,'13534567855')";
                //执行SQL语句并接收结果
                int result1 = statement.executeUpdate(sql1);
                if (result1 == 1) {
                    System.out.println("插入数据成功！");
                } else {
                    System.out.println("插入数据失败！");
                }
                System.out.println("****************修改数据*****************");
                String sql3 = "update student set name='孙悟空003' where id='2023001'";
                int result3 = statement.executeUpdate(sql3);
                if (result3 == 1) {
                    System.out.println("修改数据成功！");
                } else {
                    System.out.println("修改数据失败！");
                }
                System.out.println("*******************删除数据****************");
                String sql4 = "delete from student where id='2023002'";
                int result4 = statement.executeUpdate(sql4);
                if (result4 == 1) {
                    System.out.println("删除数据成功！");
                } else {
                    System.out.println("删除数据失败！");
                }
                System.out.println("************查询数据*******************");
                //查询数据
                String sql2 = "select * from student";
                result2 = statement.executeQuery(sql2);
                //处理结果集
                while (result2.next()) {
                    //有数据，依据列名获取数据
                    Integer id = result2.getInt("id");
                    String name = result2.getString("name");
                    String sex = result2.getString("sex");
                    Integer age = result2.getInt("age");
                    String tel = result2.getString("tel");
                    System.out.println("学号：" + id + "姓名：" + name + "性别：" + sex + "年龄：" + age + "电话：" + tel);
                }
                result2.close();
            } else {
                System.out.println("连接失败");
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (statement != null) {
                statement.close();
            }
            if (conn != null) {
                conn.close();
            }
            if (result2 != null) {
                result2.close();
            }
        }

    }
}
